﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using LarDosIdosos.Business;
using LarDosIdosos.Business.Model;
using LarDosIdosos.Properties;

namespace LarDosIdosos.DataSources
{
    public class DataSourceResponsavel
    {
        public List<Business.Model.Responsavel> BuscaResponsaveis(int startrowindex, int maximumrows, string sortexpression, object sql)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            return dbResponsavel.SelectResponsaveis(sql.ToString());
        }

        public int TotalDeResponsavel(object sql)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            return dbResponsavel.Count(sql.ToString());
        }

        public Business.Model.Responsavel BuscaResponsavel(int Codigo_Responsavel)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            string sql = string.Format("select * from responsavel where Codigo_Responsavel = {0}", Codigo_Responsavel);
            return dbResponsavel.SelectResponsavel(sql);
        }
        public void Delete(object Codigo_Responsavel)
        {
            DBTelefone dbTelefone = new DBTelefone(Settings.Default.StringDeConexaoComBanco);
            dbTelefone.Delete(string.Format("delete from telefone where responsavel_codigo_responsavel = {0}",
                                            Codigo_Responsavel));
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            string sql = string.Format("delete from responsavel where Codigo_Responsavel = {0}", Codigo_Responsavel);
            dbResponsavel.DeleteResponsavel(sql);
        }

        public List<Business.Model.Idoso> BuscaIdososDoResponsavel(int codigoResponsavel)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            return dbResponsavel.BuscaIdosos(codigoResponsavel);
        }

        public int UpdateResponsavel(Business.Model.Responsavel responsavel)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            string sql = string.Format("update {0} set Nome_Responsavel = \"{1}\", CPF_Responsavel = \"{2}\", RG_Responsavel = \"{3}\", Data_de_Nascimento_Responsavel = \"{4}\", Nome_do_Pai_Responsavel = \"{5}\", Nome_da_Mae_Responsavel = \"{6}\", Estado_Civil_Codigo_Estado_Civil = {7} where Codigo_Responsavel = {8}",
                    "responsavel", responsavel.Nome_Responsavel, responsavel.CPF_Responsavel, responsavel.RG_Responsavel, responsavel.Data_de_Nascimento_Responsavel.ToString("yyyy/MM/dd"), responsavel.Nome_do_Pai_Responsavel, responsavel.Nome_da_Mae_Responsavel, responsavel.Estado_Civil.Codigo_Estado_Civil, responsavel.Codigo_Responsavel);
            return dbResponsavel.UpdateResponsavel(sql);
        }

        public int InsertResponsavel(Business.Model.Responsavel responsavel)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            string sql =
                string.Format("insert into responsavel (Nome_Responsavel, CPF_Responsavel, RG_Responsavel, Data_de_Nascimento_Responsavel, Nome_do_Pai_Responsavel, Nome_da_Mae_Responsavel, Estado_Civil_Codigo_Estado_Civil) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", responsavel.Nome_Responsavel, responsavel.CPF_Responsavel, responsavel.RG_Responsavel, responsavel.Data_de_Nascimento_Responsavel.ToString("yyyy/MM/dd"), responsavel.Nome_do_Pai_Responsavel, responsavel.Nome_da_Mae_Responsavel, responsavel.Estado_Civil.Codigo_Estado_Civil);

            return dbResponsavel.InsertResponsavel(sql);
        }

        public List<Business.Model.Responsavel> BuscaResponsaveisDoIdoso(int codigoIdoso)
        {
            DBIdosos dbIdosos = new DBIdosos(Settings.Default.StringDeConexaoComBanco);
            return dbIdosos.BuscaResponsaveisDoIdoso(codigoIdoso);
        }

        public List<Business.Model.Responsavel> BuscaTodosResponsaveis()
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            return dbResponsavel.SelectResponsaveis("select * from responsavel");
        }

        public Business.Model.Responsavel BuscaResponsavelPeloCPF(string cpfResponsavel)
        {
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            string sql = string.Format("select * from responsavel where CPF_Responsavel = {0}", cpfResponsavel);
            return dbResponsavel.SelectResponsavel(sql);
        }

        public void InsertTelefone(Business.Model.Responsavel responsavel)
        {
            DBTelefone dbTelefone = new DBTelefone(Settings.Default.StringDeConexaoComBanco);
            DBResponsavel dbResponsavel = new DBResponsavel(Settings.Default.StringDeConexaoComBanco);
            List<Telefone> listaDosTelefonesQueJaExistemNaTabela = dbResponsavel.BuscaTelefones(responsavel.Codigo_Responsavel);
            foreach (Telefone telefone in listaDosTelefonesQueJaExistemNaTabela)
            {
                IEnumerable<Telefone> enumerable = from tel in responsavel.Telefones_Responsavel
                                                   where
                                                       (tel.Codigo_Telefone == telefone.Codigo_Telefone &&
                                                        tel.Numero_Telefone == telefone.Numero_Telefone)
                                                   select tel;
                if (enumerable.Count() == 0)
                {
                    dbTelefone.Delete(string.Format("delete from telefone where codigo_telefone = {0}", telefone.Codigo_Telefone));
                }
            }
            IEnumerable<Telefone> telefones = from tel in responsavel.Telefones_Responsavel
                                              where tel.Codigo_Telefone == 0
                                              select tel;
            foreach (var telefone in telefones)
            {
                string sql = string.Format("insert into telefone (Responsavel_Codigo_Responsavel,Numero_Telefone) values ({0},'{1}')", responsavel.Codigo_Responsavel, RemoveMascaraTelefone(telefone.Numero_Telefone));
                dbTelefone.Insert(sql);
            }
        }

        private string RemoveMascaraTelefone(string telefone)
        {
            return telefone.Replace("(", "").Replace(")", "").Replace("-", "");
        }
    }
}